﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace WaveMaster.Holidays.Club.SQL
{
    public class TicketPurchaseSQL
    {
        public static string GetAllTicketPurchase()
        {
            string sql = "select * from ticket_purchase order by m_transactionDate DESC LIMIT 100";
            return sql;
        }
        public static string GetTicketPurchaseByID()
        {
            string sql = "select * from ticket_purchase p, travel_agent a WHERE m_purchaseID = ?m_purchaseID and p.m_travelAgentId=a.m_travelAgentId";
            return sql;
        }
        public static string GetAllTicketPurchaseByAgent()
        {
            string sql = "select * from ticket_purchase where m_travelAgentId=?m_travelAgentId";
            return sql;
        }
        public static string GetCountTicketPurchaseByAgent()
        {
            string sql = "select count(*) from ticket_purchase where m_travelAgentId=?m_travelAgentId";
            return sql;
        }
        public static string SearchTicketPurchaseByCodeName(string p)
        {
            string sql = @"select p.* from ticket_purchase p, travel_agent a WHERE p.m_travelAgentId = a.m_travelAgentId and concat(p.m_code,
            p.m_transactionDate,a.m_code,a.m_name) like '%" + p + "%'";
            return sql;
        }
        public static string DeleteTicketPurchaseByID()
        {
            return @"DELETE FROM ticket_purchase
      WHERE m_purchaseID = ?m_purchaseID";
        }
        public static string InsertTicketPurchase()
        {
            string sql = @"INSERT INTO ticket_purchase
           (m_code
           ,m_transactionDate
           ,m_travelAgentId
           ,m_quantity
           ,m_unitPrice
           ,m_totalPrice
            ,m_ticketType
           ,m_cretnDate            
            ,m_cretnBy
            ,m_modfctnDate
            ,m_modfctnBy
            ,m_discount
            )
     VALUES
           (?m_code
           ,?m_transactionDate
           ,?m_travelAgentId
           ,?m_quantity
           ,?m_unitPrice
           ,?m_totalPrice
            ,?m_ticketType
           ,?m_cretnDate
            ,?m_cretnBy
            ,?m_modfctnDate
            ,?m_modfctnBy
            ,?m_discount)";
            return sql;
        }
        public static string UpdateTicketPurchase()
        {
            string sql = @"UPDATE ticket_purchase
   SET m_code =?m_code
           ,m_transactionDate=?m_transactionDate
           ,m_travelAgentId=?m_travelAgentId
           ,m_quantity=?m_quantity
           ,m_unitPrice=?m_unitPrice
           ,m_totalPrice=?m_totalPrice
            ,m_ticketType=?m_ticketType
            ,m_modfctnDate=?m_modfctnDate
            ,m_modfctnBy=?m_modfctnBy
 WHERE m_purchaseID = ?m_purchaseID";
            return sql;
        }

        public static string GetTotalPurchaseByAgentID()
        {
            string sql = @"select sum(m_quantity) from ticket_purchase 
                    where m_travelAgentId=?m_travelAgentId and m_ticketType=?m_ticketType";
            return sql;
        }

        public static string ReportTicketPurchaseByDateAndTravelAgent()
        {
            string sql = @"select * from ticket_purchase p, travel_agent ta WHERE ta.m_travelAgentId = p.m_travelAgentId and
                        m_transactionDate between ?fromdate and ?todate ";
            return sql;
        }
    }
}
